﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-05-15
 功能说明：店铺深度
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using DistributeAPI.Model;
using george.Data;
using george.Helper;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace DistributeAPI
{
    [Description("店铺深度")]
    public class DCBasModelMinQtyHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region GetList 获取店铺深度列表

        [MustLogin]
        [Description("获取店铺深度列表")]
        [ReturnType(typeof(PaperData<DCBasModelMinQtyModel.ListModel>))]
        public ApiResult GetList(
            [ParamDescription("类别")] string KindID = "",
            [ParamDescription("品类")] string ModelID = "",
            [ParamDescription("小品类")] string ItemID = "",
            [ParamDescription("店铺编号或名称")] string Shop = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 100)
        {
            try
            {
                var sqlStr = @"select {0} from ( 
select f.CompanyID, f.ShopID, f.KindID, f.ModelID, f.ItemID, f.SizeTypeFlag,f.SizeLength, sum(f.FirstDCQty)  as 'FirstDCQtySum', sum(f.MinQty) as 'MinQtySum',shop.ShopCode,shop.ShopName,kind.KindName,model.ModelName,item.ItemName,row_number()over(order by f.CompanyID,f.ShopID,f.KindID,f.ModelID,f.ItemID) as rowIndex
from ngoods.ngoods_DC_Bas_ModelMinQty f
left join ndw.ndw_Shop shop on f.CompanyID = shop.CompanyID and f.ShopID = shop.ShopID
left join nods.nods_erp_SD_Mat_Kind kind on f.ExtrSource = kind.ExtrSource and f.KindID = kind.KindID
left join nods.nods_erp_SD_Mat_Model model on f.ExtrSource = model.ExtrSource and f.ModelID = model.ModelID
left join nods.nods_erp_sd_mat_Item item on f.ExtrSource = item.ExtrSource and f.ItemID = item.ItemID
{1} 
group by f.CompanyID,f.ShopID,shop.ShopCode,shop.ShopName,f.KindID,kind.KindName,f.ModelID,model.ModelName, f.ItemID,item.ItemName, f.SizeTypeFlag,f.SizeLength
order by f.CompanyID,f.ShopID,shop.ShopCode,shop.ShopName,f.KindID,kind.KindName,f.ModelID,model.ModelName, f.ItemID,item.ItemName, f.SizeTypeFlag,f.SizeLength
) table1 
";
                string whereStr = $"where f.CompanyID = '{UserInfo.UserCompanyId}' and 1 = 1 ";
                //加入前台传来的参数
                whereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and f.KindID = '{KindID}'";
                whereStr += string.IsNullOrWhiteSpace(ModelID) ? "" : $" and f.ModelID = '{ModelID}'";
                whereStr += string.IsNullOrWhiteSpace(ItemID) ? "" : $" and f.ItemID = '{ItemID}'";
                whereStr += string.IsNullOrWhiteSpace(Shop) ? "" : $" and ( shop.ShopCode like '%{Shop.Trim()}%' or shop.ShopName like '%{Shop.Trim()}%' ) ";

                var countSql = string.Format(sqlStr, "count(1)", whereStr);
                var cmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));


                var result = PaperData<DCBasModelMinQtyModel.ListModel>.Create(count, Size, Page);
                var listSql = string.Format(sqlStr, "*", whereStr);
                if (Page != 0 && Size != 0)
                {
                    listSql += string.Format($@" where rowIndex BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size} ");
                }

                var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
                result.Items = ModelConvertHelper<DCBasModelMinQtyModel.ListModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region Insert 新增店铺深度

        [MustLogin]
        [JsonType]
        [Description("新增店铺深度")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert([ParamDescription("要插入的数据")] DCBasModelMinQtyModel.InsertModel param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.Shop))
            {
                return Error("新增失败！店铺不能为空，请输入店铺的名称或者编码");
            }

            if (string.IsNullOrWhiteSpace(param.KindID))
            {
                return Error("新增失败！类别参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(param.ModelID))
            {
                return Error("新增失败！品类参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(param.SizeTypeFlag))
            {
                return Error("新增失败！尺码类型参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(param.SizeLength))
            {
                return Error("新增失败！尺码长度参数丢失，请刷新页面重试");
            }

            foreach (var model in param.SizeList)
            {
                if (model.FirstDCQty < 0 || model.FirstDCQty > 999)
                {
                    return Error("新增失败！最低深度必须在区间[0,999]之间");
                }

                if (model.MinQty < 0 || model.MinQty > 999)
                {
                    return Error("新增失败！首配深度必须在区间[0,999]之间");
                }
            }

            int i = 0;
            foreach (var model in param.SizeList)
            {
                if (model.FirstDCQty == 0 && model.MinQty == 0)
                {
                    i++;
                }
            }
            if (i == param.SizeList.Count)
            {
                return Error("新增失败！！首配深度和最低深度至少有1个大于0");
            }

            #endregion

            #region 检测店铺是否存在并获取shopId

            var sql = $@"select ShopID from ndw.ndw_Shop where CompanyID = '{UserInfo.UserCompanyId}' and  ShopCode = '{param.Shop}' or ShopName = '{param.Shop}'  limit 1 ";
            var o = Helper.ExecuteScalar(new VerticaCommand(sql));
            if (o == null)
            {
                return Error($"新增失败！不存在店铺[{param.Shop}],请检查店铺名称");
            }
            var shopId = string.Concat(o);

            #endregion

            #region 检测该配置是否已存在，避免插入重复数据

            sql = $@"select ShopID from ngoods.ngoods_DC_Bas_ModelMinQty 
where CompanyID = '{UserInfo.UserCompanyId}' and ShopID = '{shopId}' and KindID = '{param.KindID}' and ModelID = '{param.ModelID}' and ItemID = '{param.ItemID}' and SizeTypeFlag = '{param.SizeTypeFlag}' and SizeLength = '{param.SizeLength}' 
limit 1 ";

            o = Helper.ExecuteScalar(new VerticaCommand(sql));
            if (o != null)
            {
                return Error("新增失败！\r\n该店铺下同类别、同品类、同小品类、同尺码类型的配置已存在！");
            }

            #endregion

            StringBuilder sb = new StringBuilder();

            #region 删除已存在的相同的最低深度
            sql = $@"delete from ngoods.ngoods_DC_Bas_ModelMinQty 
where CompanyID = '{UserInfo.UserCompanyId}' and ShopID = '{shopId}' and KindID = '{param.KindID}' and ModelID = '{param.ModelID}' and 
ItemID = '{param.ItemID}' and SizeTypeFlag = '{param.SizeTypeFlag}' and  SizeLength = '{param.SizeLength}' ; ";

            sb.AppendLine(sql);
            #endregion

            #region 插入新数据

            //插入首配深度和最低深度
            foreach (var model in param.SizeList)
            {
                sql = $@"insert into ngoods.ngoods_DC_Bas_ModelMinQty(ExtrSource,CompanyID, ShopID, KindID, ModelID,ItemID,SizeID, SizeTypeFlag, FirstDCQty, MinQty, AllowUsed,SizeLength) 
values
('{UserInfo.UserExtrSource}','{UserInfo.UserCompanyId}', '{shopId}', '{param.KindID}', '{param.ModelID}','{param.ItemID}','{model.SizeID}','{param.SizeTypeFlag}',{model.FirstDCQty}, {model.MinQty}, 1,'{param.SizeLength}'); 
";
                sb.AppendLine(sql);
            }

            #endregion
            int n = Helper.ExecuteSqlTran(new List<CommandInfo>() { new CommandInfo(sb.ToString(), null) });
            if (n > 0)
            {
                return Success(0);
            }
            return Error("新增失败,请重试");
        }

        #endregion

        #region Update 更新店铺深度

        [MustLogin]
        [JsonType]
        [Description("更新店铺深度")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("公司代码")] DCBasModelMinQtyModel.UpdateModel param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.Shop))
            {
                return Error("更新失败！店铺不能为空，请输入店铺的名称或者编码");
            }

            if (string.IsNullOrWhiteSpace(param.KindID))
            {
                return Error("更新失败！类别参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(param.ModelID))
            {
                return Error("更新失败！尺码类型参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(param.SizeTypeFlag))
            {
                return Error("更新失败！尺码类型丢失，请刷新页面重试");
            }
            if (string.IsNullOrWhiteSpace(param.SizeLength))
            {
                return Error("更新失败！尺码长度丢失，请刷新页面重试");
            }

            if (param.SizeList.Count == 0)
            {
                return Error("更新失败！首配深度和最低深度至少有1个大于0");
            }

            int i = 0;
            foreach (var model in param.SizeList)
            {
                if (model.FirstDCQty == 0 && model.MinQty == 0)
                {
                    i++;
                }
            }
            if (i == param.SizeList.Count)
            {
                return Error("更新失败！首配深度和最低深度至少有1个大于0");
            }


            //这几个是原始的值，会用来做Update，如果有缺失，就不用继续下去了
            if (string.IsNullOrWhiteSpace(param.OriginalCompanyID) || string.IsNullOrWhiteSpace(param.OriginalShop) ||
                string.IsNullOrWhiteSpace(param.OriginalKindID) || string.IsNullOrWhiteSpace(param.OriginalModelID) ||
                string.IsNullOrWhiteSpace(param.OriginalItemID) || string.IsNullOrWhiteSpace(param.OriginalSizeTypeFlag) ||
                string.IsNullOrWhiteSpace(param.SizeLength))
            {
                return Error("参数解析失败，请刷新重试");
            }

            #endregion

            #region 检测店铺是否存在并获取shopId

            var sql = $@"select ShopID from ndw.ndw_Shop dw where CompanyID = '{UserInfo.UserCompanyId}' and (ShopCode = '{param.Shop}' or ShopName = '{param.Shop}')  limit 1 ";
            var o = Helper.ExecuteScalar(new VerticaCommand(sql));
            if (o == null)
            {
                return Error($"不存在店铺[{param.Shop}],请检查店铺名称");
            }
            var shopId = string.Concat(o);

            #endregion

            #region 检测该配置是否已存在，避免变成重复数据

            //拼凑key，如果6个都一样证明是在改自身，不用判断重复，否则就要判断
            if (param.CompanyID != param.OriginalCompanyID || shopId != param.OriginalShop ||
                param.KindID != param.OriginalKindID || param.ModelID != param.OriginalModelID ||
                param.ItemID != param.OriginalItemID || param.SizeTypeFlag != param.OriginalSizeTypeFlag || 
                param.SizeLength != param.OriginalSizeLength)
            {
                sql = $@"select ShopID from goods.goods_DC_Bas_FirstDCQty 
where  CompanyID = '{UserInfo.UserCompanyId}' and ShopID = '{shopId}' and KindID = '{param.KindID}' and ModelID = '{param.ModelID}' and ItemID = '{param.ItemID}' and 
SizeLength = '{param.SizeLength}' limit 1 ";

                o = Helper.ExecuteScalar(new VerticaCommand(sql));
                if (o != null)
                {
                    return Error("修改失败！\r\n该店铺下同类别、同品类、同尺码类型的配置已存在！");
                }
            }

            #endregion

            #region 插入新数据
            var list = new List<CommandInfo>();

            //批量删除
            sql = $@"delete from ngoods.ngoods_DC_Bas_ModelMinQty 
where CompanyID='{UserInfo.UserCompanyId}' and ShopID='{shopId}' and KindID='{param.OriginalKindID}' and ModelID='{param.OriginalModelID}' and ItemID= '{param.OriginalItemID}' and SizeTypeFlag='{param.OriginalSizeTypeFlag}' and SizeLength = '{param.SizeLength}' ;";
            list.Add(new CommandInfo(sql, null));


            foreach (var model in param.SizeList)
            {
                var sqlModelMinQty = $@"insert into ngoods.ngoods_DC_Bas_ModelMinQty(ExtrSource,CompanyID, ShopID, KindID, ModelID, ItemID, SizeTypeFlag, SizeID, FirstDCQty, MinQty, AllowUsed,SizeLength) 
values('{UserInfo.UserExtrSource}','{UserInfo.UserCompanyId}', '{shopId}', '{param.KindID}', '{param.ModelID}', '{param.ItemID}', '{param.SizeTypeFlag}','{model.SizeID}',{model.FirstDCQty}, {model.MinQty}, 1,'{param.SizeLength}') ;
";
                list.Add(new CommandInfo(sqlModelMinQty, null));
            }

            #endregion

            int n = Helper.ExecuteSqlTran(list);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }

        #endregion

        #region GetDetail 获取深度明细

        [MustLogin]
        [JsonType]
        [Description("获取深度明细")]
        [ReturnType(typeof(PaperData<DCBasModelMinQtyModel.SizeModel>))]
        public ApiResult GetDetail([ParamDescription("要获取的数据的key")] DCBasModelMinQtyModel.QueryModel param)
        {
            if (string.IsNullOrWhiteSpace(param.ShopID) || string.IsNullOrWhiteSpace(param.KindID) || string.IsNullOrWhiteSpace(param.ModelID) || string.IsNullOrWhiteSpace(param.ItemID))
            {
                return Error("参数解析失败，请刷新页面重试");
            }

            var sql = $@"select f.CompanyID,f.ShopID,shop.ShopCode,shop.ShopName,f.KindID,kind.KindName,f.ModelID,model.ModelName,f.ItemID,item.ItemName,f.FirstDCQty,f.MinQty,f.SizeTypeFlag,f.SizeLength
from ngoods.ngoods_DC_Bas_ModelMinQty f
left join ndw.ndw_Shop shop on f.CompanyID = shop.CompanyID and f.ShopID = shop.ShopID
left join nods.nods_erp_SD_Mat_Kind kind on f.ExtrSource = kind.ExtrSource and  f.KindID = kind.KindID
left join nods.nods_erp_SD_Mat_Model model on f.ExtrSource = model.ExtrSource and f.ModelID = model.ModelID
left join nods.nods_erp_sd_mat_Item item on f.ExtrSource = item.ExtrSource and f.ItemID = item.ItemID
where f.CompanyID = '{UserInfo.UserCompanyId}' and f.ShopID = '{param.ShopID}' and f.KindID = '{param.KindID}' and f.ModelID = '{param.ModelID}' and f.ItemID = '{param.ItemID}' and f.SizeTypeFlag = '{param.SizeTypeFlag}' and SizeLength = '{param.SizeLength}' 
";

            var dt = Helper.ExecuteDataTable(new VerticaCommand(sql, null));
            var list = ModelConvertHelper<DCBasModelMinQtyModel.QueryModel>.ConvertToModel(dt).ToList();

            if (list.Count > 0)
            {
                var model = list[0];

                sql = $@"select tb.SizeID,tb.SizeName,isnull(m.FirstDCQty,0) as FirstDCQty ,isnull(m.MinQty,0) as MinQty 
from 
( 
    select a.ExtrSource,b.SizeID,b.SizeName,b.SizeCodeSort 
    from ngoods.ngoods_PL_Bas_ModelSizeType a ,nods.nods_erp_SD_Mat_Size b 
    where a.ExtrSource =  b.ExtrSource and a.CompanyID = '{UserInfo.UserCompanyId}' and a.ExtrSource = '{UserInfo.UserExtrSource}' and  ModelID='{model.ModelID}' and a.SizeTypeFlag = '{param.SizeTypeFlag}' and a.SizeTypeFlag=b.SizeCodeType and b.AllowUsed=1
) tb
left join ngoods.ngoods_DC_Bas_ModelMinQty m 
on m.ExtrSource = tb.ExtrSource and tb.SizeID = m.SizeID and m.CompanyID = '{model.CompanyID}' and m.ShopID = '{model.ShopID}' and m.KindID = '{model.KindID}' and m.ModelID = '{model.ModelID}' and m.ItemID = '{param.ItemID}'  and m.SizeTypeFlag = '{param.SizeTypeFlag}' and SizeLength = '{param.SizeLength}' 
where CompanyID = '{UserInfo.UserCompanyId}' 
order by tb.SizeCodeSort ";

                var sizedt = Helper.ExecuteDataTable(new VerticaCommand(sql, null));
                var sizeList = ModelConvertHelper<DCBasModelMinQtyModel.SizeModel>.ConvertToModel(sizedt).ToList();

                if (sizeList.Count > 0)
                {
                    model.SizeList = sizeList;
                }
                return Success(model);
            }
            return Error("数据获取失败，请刷新页面重试");
        }

        #endregion

        #region GetSizeList 获取最低尺码列表

        [MustLogin]
        [Description("获取最低尺码列表")]
        [ReturnType(typeof(PaperData<DCBasModelMinQtyModel.SizeModel>))]
        public ApiResult GetSizeList(
            [ParamDescription("公司代码")] string CompanyID = "",
            [ParamDescription("店铺ID")] string ShopID = "",
            [ParamDescription("类别")] string KindID = "",
            [ParamDescription("尺码类型")] string SizeTypeFlag = "",
            [ParamDescription("品类")] string ModelID = "",
            [ParamDescription("尺码长度")] string SizeLength = "")
        {
            try
            {
                var listSql = $@"select distinct tb.SizeCodeSort,tb.SizeID,tb.SizeName,isnull(m.MinQty,0) as MinQty,isnull(m.FirstDCQty,0) as FirstDCQty 
from 
( 
    select a.ExtrSource,b.SizeID,b.SizeName,b.SizeCodeSort 
    from ngoods.ngoods_PL_Bas_ModelSizeType a ,nods.nods_erp_SD_Mat_Size b 
    where a.ExtrSource = b.ExtrSource and  ModelID='{ModelID}' and SizeTypeFlag = '{SizeTypeFlag}'  and a.SizeTypeFlag=b.SizeCodeType and b.AllowUsed=1
) tb
left join ngoods.ngoods_DC_Bas_ModelMinQty m 
on tb.SizeID = m.SizeID and m.CompanyID = '{CompanyID}' and m.ShopID = '{ShopID}' and m.KindID = '{KindID}' and m.ModelID = '{ModelID}' and m.SizeTypeFlag='{SizeTypeFlag}' and  SizeLength = '{SizeLength}'
where tb.ExtrSource = '{UserInfo.UserExtrSource}'
order by tb.SizeCodeSort ";
                var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
                var list = ModelConvertHelper<DCBasModelMinQtyModel.SizeModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region GetSizeTypeFlag 获取尺码类型列表

        [MustLogin]
        [Description("获取尺码类型")]
        [ReturnType(typeof(PaperData<DCBasModelMinQtyModel.SizeTypeFlagModel>))]
        public ApiResult GetSizeTypeFlag([ParamDescription("公司代码")] string CompanyID = "", [ParamDescription("品类")] string ModelID = "")
        {
            try
            {
                var listSql = $@"select SizeTypeFlag from goods.goods_PL_Bas_ModelSizeType where CompanyID = '{UserInfo.UserCompanyId}' and ModelID = '{ModelID}' ";
                var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
                var list = ModelConvertHelper<DCBasModelMinQtyModel.SizeTypeFlagModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion


        #region ImportExcel 导入店铺深度

        /// <summary>
        /// 导入店铺深度
        /// </summary>
        /// <param name="list"></param>
        public void ImportExcel(IList<DCBasModelMinQtyModel.ImportExcelModel> list)
        {
            //var l = list.GroupBy(x => new {x.ShopCode, x.KindName, x.ModelName, x.ItemName, x.SizeTypeFlag});


            #region 先取出基本资料，方便用Name取ID，不用一次次查询数据库

            //店铺
            var dtShop = Helper.ExecuteDataTable(new VerticaCommand($"select ShopID,ShopCode from ndw.ndw_Shop where CompanyID = '{UserInfo.UserCompanyId}'", null));
            var listShop = ModelConvertHelper<ShopClass>.ConvertToModel(dtShop);

            //类别
            var dtKind = Helper.ExecuteDataTable(new VerticaCommand($"select KindID,KindName from nods.nods_erp_sd_mat_kind where ExtrSource = '{UserInfo.UserExtrSource}'", null));
            var listKind = ModelConvertHelper<KindClass>.ConvertToModel(dtKind);

            //品类
            var dtModel = Helper.ExecuteDataTable(new VerticaCommand($"select ModelID,ModelName from nods.nods_erp_sd_mat_model where ExtrSource = '{UserInfo.UserExtrSource}'", null));
            var listModel = ModelConvertHelper<ModelClass>.ConvertToModel(dtModel);

            //小品类
            var dtItem = Helper.ExecuteDataTable(new VerticaCommand($"select ModelID,ItemID,ItemName from ngoods.ngoods_PL_ModelItem where ExtrSource = '{UserInfo.UserExtrSource}'", null));
            var listItem = ModelConvertHelper<ItemClass>.ConvertToModel(dtItem);

            //尺码类型
            var dtSizeType = Helper.ExecuteDataTable(new VerticaCommand($"select ModelID,SizeTypeFlag from ngoods.ngoods_PL_Bas_ModelSizeType where CompanyID = '{UserInfo.UserCompanyId}'", null));
            var listSizeType = ModelConvertHelper<SizeTypeFlagClass>.ConvertToModel(dtSizeType);

            //尺码
            var dtSize = Helper.ExecuteDataTable(new VerticaCommand($"select SizeID,SizeName,SizeCodeType from nods.nods_erp_SD_Mat_Size  where ExtrSource = '{UserInfo.UserExtrSource}'", null));
            var listSize = ModelConvertHelper<SizeClass>.ConvertToModel(dtSize);

            #endregion

            var deleteCmdList = new List<CommandInfo>();
            //一条一条的删，太慢，这里用 and 连接拼接查询，以减少查询的执行次数，加快处理速度
            //每1000条执行一次SQL
            int max = 1000;
            for (int i = 0; i < list.Count; i++)
            {
                var sb = new StringBuilder($@"delete from ngoods.ngoods_DC_Bas_ModelMinQty where CompanyID = '{UserInfo.UserCompanyId}' and");
                for (int j = 1; j <= max; j++)
                {
                    if (list[i].SizeName == "2XL")
                    {
                        list[i].SizeName = "XXL";
                    }
                    var shop = listShop.FirstOrDefault(x => x.ShopCode == list[i].ShopCode.Trim());
                    var kind = listKind.FirstOrDefault(x => x.KindName == list[i].KindName.Trim());
                    var model = listModel.FirstOrDefault(x => x.ModelName == list[i].ModelName.Trim());

                    #region 检测输入的值是否正确

                    if (shop == null)
                    {
                        throw new Exception($"店铺编码[{list[i].ShopCode}]不存在");
                    }
                    if (kind == null)
                    {
                        throw new Exception($"类别名称[{list[i].KindName}]不存在");
                    }
                    if (model == null)
                    {
                        throw new Exception($"品类名称[{list[i].ModelName}]不存在");
                    }
                    //该品类下是否有这个小品类
                    var item = listItem.FirstOrDefault(x => x.ModelID == model.ModelID && x.ItemName == list[i].ItemName.Trim());
                    if (item == null)
                    {
                        throw new Exception($"品类[{model.ModelName}]下不存在小品类[{list[i].ItemName}]");
                    }
                    //该品类下是否有这个尺码类型
                    var sizeType = listSizeType.FirstOrDefault(x => x.ModelID == model.ModelID && x.SizeTypeFlag == list[i].SizeTypeFlag.Trim());
                    if (sizeType == null)
                    {
                        throw new Exception($"品类[{model.ModelName}]下不存在尺码类型[{list[i].SizeTypeFlag}]");
                    }
                    //该尺码类型下是否有这个尺码名称
                    var size = listSize.FirstOrDefault(x => sizeType.SizeTypeFlag == x.SizeCodeType && x.SizeName == list[i].SizeName.Trim());
                    if (size == null)
                    {
                        throw new Exception($"尺码名称[{list[i].SizeName}]不存在");
                    }

                    var tempList = list.GroupBy(x => new {x.ShopCode, x.KindName, x.ModelName, x.ItemName, x.SizeTypeFlag, x.SizeLength})
                        .Select(group => new
                        {
                            group.Key.SizeLength, Count = group.Count()
                        });

                    foreach (var t in tempList)
                    {
                        var lenStr = t.SizeLength.Trim().Replace(" ", "").Replace("码", "");
                        var len = 0;
                        if (int.TryParse(lenStr, out len))
                        {
                            if (len != t.Count)
                            {
                                throw new Exception($"尺码长度[{t.SizeLength}]无法导入[{t.Count}]条数据");
                            }
                        }
                    }

                    #endregion

                    #region 拼接删除SQL(不到两个深度，只到尺码长度)

                    sb.AppendLine($@"(ShopID = '{shop.ShopID}' and KindID = '{kind.KindID}' and ModelID = '{model.ModelID}' and ItemID = '{item.ItemID}' and SizeTypeFlag = '{sizeType.SizeTypeFlag}' and SizeLength = '{list[i].SizeLength}' )");
                    //最后一个不要or
                    if (i < list.Count - 1 && j < max)
                    {
                        sb.Append(" or ");
                    }
                    i++;
                    if (i == list.Count)
                    {
                        break;
                    }

                    #endregion
                }

                deleteCmdList.Add(new CommandInfo(sb.ToString(), null));
            }


            try
            {
                var newList = new List<DCBasModelMinQtyModel.Entity>();
                foreach (var o in list)
                {
                    //if (o.FirstDCQty == 0 && o.MinQty == 0)
                    //{
                    //    continue;
                    //}

                    if (o.SizeName == "2XL")
                    {
                        o.SizeName = "XXL";
                    }
                    var shop = listShop.FirstOrDefault(x => x.ShopCode == o.ShopCode.Trim());
                    var kind = listKind.FirstOrDefault(x => x.KindName == o.KindName.Trim());
                    var model = listModel.FirstOrDefault(x => x.ModelName == o.ModelName.Trim());
                    var size = listSize.FirstOrDefault(x => x.SizeName == o.SizeName.Trim());
                    var item = listItem.FirstOrDefault(x => x.ItemName == o.ItemName.Trim());

                    var entity = new DCBasModelMinQtyModel.Entity()
                    {
                        ExtrSource = UserInfo.UserExtrSource,
                        CompanyID = UserInfo.UserCompanyId,
                        ShopID = shop.ShopID,
                        KindID = kind.KindID,
                        ModelID = model.ModelID,
                        ItemId = item.ItemID,
                        SizeID = size.SizeID,
                        FirstDCQty = o.FirstDCQty,
                        MinQty = o.MinQty,
                        SizeTypeFlag = o.SizeTypeFlag,
                        SizeLength = o.SizeLength,
                        AllowUsed = 1
                    };
                    newList.Add(entity);
                }

                DataTable dt = DataTableHelper.LinqToDataTable(newList);
                Helper.ExecuteSqlTran(deleteCmdList, dt, "ngoods.ngoods_DC_Bas_ModelMinQty");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 临时类

        class ShopClass
        {
            public string ShopID { get; set; }
            public string ShopCode { get; set; }
        }

        class KindClass
        {
            public string KindID { get; set; }
            public string KindName { get; set; }
        }

        class ModelClass
        {
            public string ModelID { get; set; }
            public string ModelName { get; set; }
        }

        class ItemClass
        {
            public string ModelID { get; set; }
            public string ItemID { get; set; }
            public string ItemName { get; set; }
        }


        class SizeClass
        {
            public string SizeID { get; set; }
            public string SizeName { get; set; }

            public string SizeCodeType { get; set; }

        }

        class SizeTypeFlagClass
        {
            public string ModelID { get; set; }
            public string SizeTypeFlag { get; set; }
        }


        #endregion

    }
}
